﻿Imports Control_Vuelos_BE
Imports System.Data.SqlClient

Public Class UsuarioADO
    ReadOnly MiConexion As New ConexionADO()
    Dim scriterio As String = ""

    Public Function pr_ValidarUsuario(ByVal p_coduser As String, ByVal p_pswuser As String) As Boolean
        Try
            Dim cn As New SqlConnection(MiConexion.GetCnx)
            Using cn
                Dim cmd As New SqlCommand("pr_validarUsuario", cn)
                Using cmd
                    cn.Open()
                    cmd.CommandType = Data.CommandType.StoredProcedure
                    cmd.Parameters.Add("@pscodusr", SqlDbType.VarChar, 20)
                    cmd.Parameters.Add("@pspswusr", SqlDbType.VarChar, 20)
                    cmd.Parameters("@pscodusr").Value = Trim(p_coduser)
                    cmd.Parameters("@pspswusr").Value = Trim(p_pswuser)
                    cmd.Parameters.Add("@pnretval", SqlDbType.Int, 8)
                    cmd.Parameters("@pnretval").Direction = ParameterDirection.Output
                    cmd.ExecuteScalar()
                    If cmd.Parameters("@pnretval").Value = 200 Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch myException As Exception
            MsgBox(" >> " & myException.Message, MsgBoxStyle.Critical)
            Return False
        End Try
    End Function

    Public Function pr_Select_Usuarios(ByRef p_key As String) As DataTable
        Try
            Const sql As String = "SELECT * FROM dbo.Usuario "
            scriterio = IIf(p_key.Trim() = "", "", " Where  " & Trim(p_key))
            Dim cn As New SqlConnection(MiConexion.GetCnx)
            Using cn
                Dim da As SqlDataAdapter = New SqlDataAdapter(sql & scriterio, cn)
                Using da
                    cn.Open()
                    Dim dt As DataTable = New DataTable("User")
                    ' Rellenamos el objeto DataTable
                    da.Fill(dt)
                    Return dt
                End Using
            End Using

        Catch myException As Exception
            MsgBox("Usuario (pr_Select_Usuarios) --> " & myException.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function pr_Get_Usuario(ByRef p_coduser As String) As Usuario
        Try
            Const sql As String = "SELECT * FROM dbo.Usuario WITH (NOLOCK) "
            scriterio = String.Format(" Where  coduser = '{0}'", p_coduser.Trim)
            Dim drCurrent As DataRow
            Dim cn As New SqlConnection(MiConexion.GetCnx)
            Dim rstUsuario As New Usuario
            Using cn
                Dim da As SqlDataAdapter = New SqlDataAdapter(sql & scriterio, cn)
                Using da
                    cn.Open()
                    Dim dt As DataTable = New DataTable("Usuario")
                    ' Rellenamos el objeto DataTable
                    da.Fill(dt)
                    For Each drCurrent In dt.Rows
                        rstUsuario.coduser = drCurrent("coduser").ToString
                        rstUsuario.nomuser = drCurrent("nomuser").ToString
                        rstUsuario.codnive = drCurrent("codnive").ToString
                        rstUsuario.maiuser = drCurrent("maiuser").ToString
                        rstUsuario.desuser = drCurrent("desuser").ToString
                        rstUsuario.fchingr = IIf(drCurrent("fchingr") Is DBNull.Value, Nothing, drCurrent("fchingr"))
                        rstUsuario.fchterm = IIf(drCurrent("fchterm") Is DBNull.Value, Nothing, drCurrent("fchterm"))
                        rstUsuario.flgLock = IIf(drCurrent("flgLock") Is DBNull.Value, 0, drCurrent("flgLock"))
                        rstUsuario.flglgn = IIf(drCurrent("flglgn") Is DBNull.Value, 0, drCurrent("flglgn"))
                        rstUsuario.flguser = drCurrent("flguser")
                    Next
                    Return rstUsuario
                End Using
            End Using
        Catch myException As Exception
            MsgBox("Usuario (pr_Get_Usuario) --> " & myException.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function pr_AgregarUsuario(ByVal p_UsuarioBE As Usuario) As Boolean
        Try
            Dim sInstruccion As String = ""
            Dim cn As New SqlConnection(MiConexion.GetCnx)
            Dim oTran As SqlTransaction
            Using cn
                cn.Open()
                oTran = cn.BeginTransaction(IsolationLevel.Serializable)
                sInstruccion = "INSERT INTO dbo.Usuario(coduser,nomuser,desuser,fchterm,fchingr,codnive,flguser,maiuser,pswuser) " & _
                    " VALUES (@coduser,@nomuser,@desuser,@fchterm,@fchingr,@codnive,@flguser,@maiuser,PWDENCRYPT(@pswuser)) "
                Dim cmd As New SqlCommand(sInstruccion, cn)
                Using cmd
                    cmd.Transaction = oTran
                    cmd.Parameters.AddWithValue("@coduser", p_UsuarioBE.coduser)
                    cmd.Parameters.AddWithValue("@nomuser", p_UsuarioBE.nomuser)
                    cmd.Parameters.AddWithValue("@desuser", p_UsuarioBE.desuser)
                    cmd.Parameters.AddWithValue("@fchterm", p_UsuarioBE.fchterm)
                    cmd.Parameters.AddWithValue("@fchingr", p_UsuarioBE.fchingr)
                    cmd.Parameters.AddWithValue("@codnive", p_UsuarioBE.codnive)
                    cmd.Parameters.AddWithValue("@flguser", p_UsuarioBE.flguser)
                    cmd.Parameters.AddWithValue("@maiuser", p_UsuarioBE.maiuser)
                    cmd.Parameters.AddWithValue("@pswuser", p_UsuarioBE.pswuser)
                    cmd.ExecuteNonQuery()
                End Using
                oTran.Commit()
            End Using
            Return True
        Catch myException As Exception
            MsgBox("UsuarioADO (pr_AgregarUsuario) --> " & myException.Message, MsgBoxStyle.Critical)
            Return False
        End Try
    End Function

    Public Function pr_RestablecerContrasena(p_coduser As String, p_pswuser As String) As Boolean
        Try
            Dim sInstruccion As String = ""
            Dim cn As New SqlConnection(MiConexion.GetCnx)
            Dim oTran As SqlTransaction
            Using cn
                cn.Open()
                oTran = cn.BeginTransaction(IsolationLevel.Serializable)
                sInstruccion = "UPDATE dbo.Usuario SET pswuser = PWDENCRYPT(@pswuser) WHERE coduser = @coduser"
                Dim cmd As New SqlCommand(sInstruccion, cn)
                Using cmd
                    cmd.Transaction = oTran
                    cmd.Parameters.AddWithValue("@coduser", p_coduser)
                    cmd.Parameters.AddWithValue("@pswuser", p_pswuser)
                    cmd.ExecuteNonQuery()
                End Using
                oTran.Commit()
            End Using
            Return True
        Catch myException As Exception
            MsgBox("UsuarioADO (pr_RestablecerContrasena) --> " & myException.Message, MsgBoxStyle.Critical)
            Return False
        End Try
    End Function

    Public Function pr_ActivarDesactivarUsuario(p_coduser As String, p_flguser As String) As Boolean
        Try
            Dim sInstruccion As String = ""
            Dim cn As New SqlConnection(MiConexion.GetCnx)
            Dim oTran As SqlTransaction
            Using cn
                cn.Open()
                oTran = cn.BeginTransaction(IsolationLevel.Serializable)
                sInstruccion = "UPDATE dbo.Usuario SET flguser = @flguser WHERE coduser = @coduser"
                Dim cmd As New SqlCommand(sInstruccion, cn)
                Using cmd
                    cmd.Transaction = oTran
                    cmd.Parameters.AddWithValue("@coduser", p_coduser)
                    cmd.Parameters.AddWithValue("@flguser", p_flguser)
                    cmd.ExecuteNonQuery()
                End Using
                oTran.Commit()
            End Using
            Return True
        Catch myException As Exception
            MsgBox("UsuarioADO (pr_ActivarDesactivarUsuario) --> " & myException.Message, MsgBoxStyle.Critical)
            Return False
        End Try
    End Function

    Public Function pr_EditarUsuario(ByVal p_UsuarioBE As Usuario) As Boolean
        Try
            Dim sInstruccion As String = ""
            Dim cn As New SqlConnection(MiConexion.GetCnx)
            Dim oTran As SqlTransaction
            Using cn
                cn.Open()
                oTran = cn.BeginTransaction(IsolationLevel.Serializable)
                sInstruccion = "UPDATE dbo.Usuario SET nomuser = @nomuser,fchterm = @fchterm,codnive = @codnive ,maiuser = @maiuser, flglock =@flglock , flglgn = @flglgn  WHERE coduser = @coduser "
                Dim cmd As New SqlCommand(sInstruccion, cn)
                Using cmd
                    cmd.Transaction = oTran
                    cmd.Parameters.AddWithValue("@coduser", p_UsuarioBE.coduser)
                    cmd.Parameters.AddWithValue("@nomuser", p_UsuarioBE.nomuser)
                    cmd.Parameters.AddWithValue("@fchterm", IIf(p_UsuarioBE.fchterm = Nothing, DBNull.Value, p_UsuarioBE.fchterm))
                    'cmd.Parameters.AddWithValue("@fchterm", DBNull.Value)
                    cmd.Parameters.AddWithValue("@codnive", p_UsuarioBE.codnive)
                    cmd.Parameters.AddWithValue("@maiuser", p_UsuarioBE.maiuser)
                    cmd.Parameters.AddWithValue("@flglock", p_UsuarioBE.flgLock)
                    cmd.Parameters.AddWithValue("@flglgn", p_UsuarioBE.flglgn)
                    cmd.ExecuteNonQuery()
                End Using
                oTran.Commit()
            End Using
            Return True
        Catch myException As Exception
            MsgBox("UsuarioADO (pr_EditarUsuario) --> " & myException.Message, MsgBoxStyle.Critical)
            Return False
        End Try
    End Function

End Class
